package com.supermap.dataservice.cloud.tracesource;


import com.supermap.dubbo.api.cloud.tracesource.TraceaBility;
import com.supermap.dubbo.api.cloud.tracesource.TraceabilityAudit;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description
 * @date 2018年12月21 上午 9:49
 * @email 1301584033@qq.com
 */
@Mapper
public interface TraceSourceMapper {
    /**
     * 获取最近一次溯源审核信息
     * */
    @Select("select * from t_traceability_audit WHERE TRATID=#{traceId} order by auditTime desc")
    TraceabilityAudit getTraceabilityAudit(@Param("traceId")Integer traceId);
    /**
     * 修改溯源
     * */
    @UpdateProvider(type = TraceSourceMapperSQL.class,method = "updateTraceSQL")
    Integer updateTrace(@Param("traceaBility")TraceaBility traceaBility);



    @Select("select t2.userId from t_traceability t1 " +
            " inner join t_farmland t2 " +
            " on t1.farm=t2.id and t1.id=#{traceId}")
    Integer getUserIdByTraceId(@Param("traceId")Integer traceId);
    @Select("select id from t_s_base_user_id where baseuserid=#{baseUserId}")
    Integer getBindUserId(String baseUserId);
    @Select("select baseuserid from t_s_base_user_id where id=#{bindUserId}")
    String getBaseUserId(String bindUserId);

    @Select("select userid from t_farmland where id=#{farmId}")
    Integer getFarmUserId(@Param("farmId") String farmId);
    @Select("select t1.user_id from t_s_user_org t1\n" +
            "inner join  t_s_depart t2\n" +
            "on t1.org_id=t2.ID and t2.depareacode=#{areaCode}")
    String getSmsUserId(@Param("areaCode") String areaCode);
    @Select("select code from t_farmland where id=#{farmId}")
    String getFarmAreaCode(@Param("farmId") String farmId);
    /**
     * 修改改溯源列表
     * */
    @Update("update t_traceability set state=#{state} where id=#{traceId}")
    Integer updateTraceaBility(@Param("traceId")Integer traceId,@Param("state") Integer state);
    /**
     * 插入修改审核
     * */
    @Insert("insert into t_traceability_audit(statue,suggest,userid,tratid) values(#{traceabilityAudit.statue},#{traceabilityAudit.suggest},#{traceabilityAudit.userid},#{traceabilityAudit.tratid})")
    Integer insertTraceAudit(@Param("traceabilityAudit") TraceabilityAudit traceabilityAudit);
    /**
     * 获取审核流程
     * */
    @Select("select     CASE   WHEN statue = 0 THEN '待审核'  \n" +
            "\t\t\t\t\t\tWHEN statue = 1 THEN '通过'\n" +
            "                  WHEN statue = 2 THEN '驳回'  end  as statue  ,suggest,userid,date_format(auditTime,'%Y-%m-%d %H:%i:%S') as auditTime from t_traceability_audit where id=#{traceAuthId}")
    Map<String,Object> getTraceAuth(@Param("traceAuthId") String traceAuthId);
    /**
     * 获取内部用户部门名称
     * */
    @Select("select t2.departname from t_s_user_org  t1 " +
            " inner join  t_s_depart t2 " +
            " on t1.org_id=t2.ID and t1.user_id=#{userId}")
    String getDepartName(@Param("userId") String userId);
    /**
     * 获取农田作物
     * */
    @Select("select parentid from t_crop t1\n" +
            "inner join t_farmcrops t2\n" +
            "on t1.code=t2.crop where t2.farmland=#{farmId}")
    Integer getCropPardIdByFarmId(@Param("farmId") String farmId);

    /**
     * 获取溯源当中的作物
     * */
    @Select("select id as pubertyId ,puberty as pubertyName,indexid,crop   from t_puberty where crop in (select parentid from t_crop t1\n" +
            "inner join t_farmcrops t2\n" +
            "on t1.code=t2.crop\n" +
            "where t2.farmland=#{farmId}) and indexid >= #{indexid}  order by indexid")
    List<Map<String,Object>> getAllCropByFarmId(@Param("farmId") String farmId,@Param("indexid") Integer indexid);



    /**
     * 获取最近一次溯源活动
     * */
    @Select("select puberty,id,date_format(starttime,'%Y-%m-%d %H:%i:%S') as starttime,indexid from\n" +
            "(\n" +
            "select puberty,id,starttime,indexid from \n" +
            "(select t1.puberty,t1.id,t3.createtime starttime ,t1.indexid from t_puberty t1\n" +
            "left join\n" +
            "(select t2.id,t2.growthperiod,t2.createtime from t_agrinfo t2 where t2.farm = #{farmId}  order by createtime) t3\n" +
            "on t1.id = t3.growthperiod\n" +
            "where t1.crop = #{code}\n" +
            "group by t1.id\n" +
            "order by t1.indexid) t1\n" +
            "union all\n" +
            "select puberty,id,starttime,indexid from \n" +
            "(select t1.puberty,t1.id,t3.uploadTime starttime,t1.indexid from t_puberty t1\n" +
            "left join\n" +
            "(select t2.id,t2.growthperiod,t2.uploadTime from t_advisoryinfo t2 where t2.farmlandid = #{farmId}  order by uploadTime) t3\n" +
            "on t1.id = t3.growthperiod\n" +
            "where t1.crop = #{code}\n" +
            "group by t1.id\n" +
            "order by t1.indexid) t2\n" +
            "\n" +
            "union all\n" +
            "\n" +
            "\n" +
            "select puberty,id,starttime,indexid from \n" +
            "(select t1.puberty,t1.id,t3.createtime starttime,t1.indexid from t_puberty t1\n" +
            "left join\n" +
            "(select t2.id,t2.growthperiod,t2.createtime from t_disaster t2 where t2.farm = #{farmId} order by createtime) t3\n" +
            "on t1.id = t3.growthperiod\n" +
            "where t1.crop = #{code}\n" +
            "group by t1.id\n" +
            "order by t1.indexid) t3\n" +
            "\n" +
            "union all\n" +
            "\n" +
            "select puberty,id,starttime,indexid from \n" +
            "(select t1.puberty,t1.id,t3.createtime starttime,t1.indexid from t_puberty t1\n" +
            "left join\n" +
            "(select t2.id,t2.growthperiod,t2.createtime from t_disaster_dip t2 where t2.farmlandid = #{farmId}  order by createtime) t3\n" +
            "on t1.id = t3.growthperiod\n" +
            "where t1.crop = #{code}\n" +
            "group by t1.id\n" +
            "order by t1.indexid) t4) t5\n" +
            "where starttime is not null\n" +
            "order by starttime desc limit 1\n" +
            ";")
    Map<String,Object> getTopTime(@Param("code") Integer code,@Param("farmId") String farmId);

    /**
     * 比对是否在此处溯源之中
     * */
    @Select("select count(1) from t_traceability where  #{time}  between starttime  and  endtime and farm = #{farmId}")
    Integer getTSTopTime(@Param("time") String time,@Param("farmId") String farmId);

    @Select("select t2.showName,t2.HeadImage,t1.`area`,t1.farmname,(select name from t_farmcrops t1 inner join t_crop t2 on t1.crop=t2.code where t1.farmland=#{farmId} ) as cropName from t_farmland  t1\n" +
            "inner join t_user t2\n" +
            "on t1.userId=t2.id\n" +
            "where t1.id=#{farmId}")
    Map<String,Object> getFarmUserByFarmId(@Param("farmId") String farmId);

    /**
     * 农田创建时间
     * */
    @Select("select date_format(createtime,'%Y-%m-%d %H:%i:%S') as createtime from t_farmland where id=#{farmId}")
    String getFarmCreaTime(@Param("farmId") String farmId);

    /**
     * 最近收获2次的时间
     * */
    @Select("select date_format(recordtime,'%Y-%m-%d %H:%i:%S') as createtime from t_farming where `type`=324 and farmlandid=#{farmId} order by recordtime desc limit 0,2 ")
    List<String> getTop2Time(@Param("farmId") String farmId);


    /**
     * 是否收获
     * */
    @Select("select count(1) from t_farming where `type`=324 and farmlandid=#{farmId} ")
    Integer isNotHarvestByFarmId(@Param("farmId") String farmId);


    /**
     * 溯源列表
     * */
    @SelectProvider(type = TraceSourceMapperSQL.class,method = "getTraceSourceListSQL")
    List<Map<String,Object>> getTraceSourceList(@Param("farmId") String farmId,@Param("areaCode")String areaCode,@Param("state")Integer state,@Param("userId")Integer userId);
    /**
     * 根据区域获取最近一次溯源的农田信息
     * */
    @Select("select t1.id,t1.farm,t2.userId,t3.showName,t3.mobile,t3.HeadImage,t2.longitude,t2.latitude,t1.name,t1.starttime,t1.endtime,t1.state,t1.url,date_format(t1.createtime,'%Y-%m-%d %H:%i:%S') as createtime from t_traceability  t1\n" +
            "                                                 inner join t_farmland t2\n" +
            "                                                 on t1.farm=t2.id\n" +
            "                                                   inner join t_user t3\n" +
            "                                                   on t2.userId=t3.id where 1=1 \n" +
            " and t2.code like concat(#{areaCode},'%') " +
            " group by t2.id order by t1.createtime desc")
    List<Map<String,Object>> getTop1TraceSourceList(@Param("areaCode")String areaCode);

    /**
     * 溯源
     * */
    @Insert("insert into t_traceability(farm,name,starttime,endtime,url,weburl) values(#{traceaBility.farm},#{traceaBility.name},#{traceaBility.starttime},#{traceaBility.endtime},#{traceaBility.url},#{traceaBility.weburl})")
    Integer insertTraceSource(@Param("traceaBility") TraceaBility traceaBility);
    @Select("select last_insert_id()")
    Integer getInsertId();

    /**
     *
     *查询农田用户的信息
     */
    @Select("select t1.userId, t1.id as farmId ,t1.farmname as farmName,t1.longitude,t1.latitude,t1.`area`," +
            "t3.showName as userName,t4.name as cropName,t4.parentid as cropId " +
            "  from t_farmland t1 " +
            "inner join t_farmcrops t2 " +
            "on t1.id = t2.farmland " +
            "inner join t_user t3 " +
            "on t1.userId = t3.id " +
            "inner join t_crop t4 " +
            "on t2.crop = t4.code " +
            "where t1.id = #{farmId};")
    TraceSoure  getFarmAndFarmByFarmId(@Param("farmId")String farmId);


    /**
     *
     *查询农田用户的信息
     */
    @Select("select t1.userId, t1.id as farmId ,t3.areaCode,t1.farmname as farmName,t1.longitude,t1.latitude,t1.`area`,\n" +
            "            t3.showName as userName,t4.name as cropName,t4.parentid as cropId,\n" +
            "            t5.legalperson,t5.cantact,t5.tel,t5.fax,t5.logo,t5.url,t5.registrationnum,\n" +
            "            case t5.companytype\n" +
            "\t\t\t    when 1 then '综合'\n" +
            "\t\t\t    when 2 then '加工'\n" +
            "\t\t\t    when 3 then '生产'\n" +
            "\t\t\t    when 4 then '流通'\n" +
            "\t\t\t    end  as companytype\n" +
            "              from t_farmland t1 \n" +
            "            inner join t_farmcrops t2 \n" +
            "            on t1.id = t2.farmland \n" +
            "            inner join t_user t3 \n" +
            "            on t1.userId = t3.id \n" +
            "            inner join t_crop t4 \n" +
            "            on t2.crop = t4.code \n" +
            "            inner join t_company t5\n" +
            "            on t3.id=t5.userid and t5.del_flg=0\n" +
            "            where t1.id = #{farmId};")
    Map<String,Object>  getUserAndFarmByFarmId(@Param("farmId")String farmId);

    /**
     *获取所有生育期
     */
    @Select("select id as code,puberty as codeName,indexid as `index` from t_puberty t where t.crop = #{codeId} order by t.indexid desc ")
    List<Puberty> getPubertyByCodeId(@Param("codeId") String codeId);
    /***
     * 获取生育期时间
     */
    @Select("select puberty,id,date_format(starttime,'%Y-%m-%d %H:%i:%S') as starttime,indexid from\n" +
            "(\n" +
            "select puberty,id,starttime,indexid from \n" +
            "(select t1.puberty,t1.id,t3.createtime starttime ,t1.indexid from t_puberty t1\n" +
            "left join\n" +
            "(select t2.id,t2.growthperiod,t2.createtime from t_agrinfo t2 where t2.farm = #{farmId} and  t2.createtime between #{beginTime} and #{endTime} order by createtime) t3\n" +
            "on t1.id = t3.growthperiod\n" +
            "where t1.crop = #{code}\n" +
            "group by t1.id\n" +
            "order by t1.indexid) t1\n" +
            "union all\n" +
            "select puberty,id,starttime,indexid from \n" +
            "(select t1.puberty,t1.id,t3.uploadTime starttime,t1.indexid from t_puberty t1\n" +
            "left join\n" +
            "(select t2.id,t2.growthperiod,t2.uploadTime from t_advisoryinfo t2 where t2.farmlandid = #{farmId} and  t2.uploadTime between #{beginTime} and #{endTime} order by uploadTime) t3\n" +
            "on t1.id = t3.growthperiod\n" +
            "where t1.crop = #{code}\n" +
            "group by t1.id\n" +
            "order by t1.indexid) t2\n" +
            "\n" +
            "union all\n" +
            "\n" +
            "\n" +
            "select puberty,id,starttime,indexid from \n" +
            "(select t1.puberty,t1.id,t3.createtime starttime,t1.indexid from t_puberty t1\n" +
            "left join\n" +
            "(select t2.id,t2.growthperiod,t2.createtime from t_disaster t2 where t2.farm = #{farmId} and  t2.createtime between #{beginTime} and #{endTime} order by createtime) t3\n" +
            "on t1.id = t3.growthperiod\n" +
            "where t1.crop = #{code}\n" +
            "group by t1.id\n" +
            "order by t1.indexid) t3\n" +
            "\n" +
            "union all\n" +
            "\n" +
            "select puberty,id,starttime,indexid from \n" +
            "(select t1.puberty,t1.id,t3.createtime starttime,t1.indexid from t_puberty t1\n" +
            "left join\n" +
            "(select t2.id,t2.growthperiod,t2.createtime from t_disaster_dip t2 where t2.farmlandid = #{farmId} and  t2.createtime between #{beginTime} and #{endTime} order by createtime) t3\n" +
            "on t1.id = t3.growthperiod\n" +
            "where t1.crop = #{code}\n" +
            "group by t1.id\n" +
            "order by t1.indexid) t4) t5\n" +
            "where starttime is not null\n" +
            "order by indexid,starttime\n" +
            ";\n")
   List<Map<String,Object>> getPubertytTime(@Param("code")String code,@Param("farmId")String farmId,@Param("beginTime")String beginTime, @Param("endTime")String endTime);
    /**
     * 获取农情
     * */
    @Select("select t1.id as objId, t1.descript as objTitle, '1' as objType , date_format(t1.createtime ,'%Y-%m-%d %H:%i:%S') as objTime,t1.longitude as objLon ,t1.latitude as objLat,\n" +
            "t1.userId as objUserId,\n" +
            "          (select name from t_crop where code=t1.breed) as objCropName,\n" +
            "            (select group_concat(imageURLContent)  from t_agrinfoimg where agrInfoId=t1.id ) as imgs, "+
            "(select showName from t_user where id=t1.userId) as objUserName,\n" +
            "(select puberty from t_puberty where id=t1.growthperiod) as objPubertyName,\n" +
            "t1.areacode as objAreaCode,\n" +
            "(select areaname from t_area where areacode= t1.areacode ) as objAreaName,\n" +
            "(select count(id) from t_agricollect where agrId= t1.id ) as collectNum,\n" +
            "(select count(id) from t_agrpraise where agrInfoId= t1.id ) as praiseNum\n" +
            " from t_agrinfo t1\n" +
            "where t1.farm=#{farmId} and t1.createtime between #{beginTime} and #{endTime} \n")
    List<ObjectTlist> getObjByFarmIdToAgr(@Param("farmId")String farmId, @Param("beginTime")String beginTime, @Param("endTime")String endTime);


    /**
     * 获取咨询
     * */
    @Select("select t1.id as objId, t1.question as objTitle, '2' as objType , date_format(t1.uploadTime ,'%Y-%m-%d %H:%i:%S') as objTime,t1.lon as objLon ,t1.lat as objLat,\n" +
            "t1.userId as objUserId,\n" +
            "             t1.subjectid as objSubjectId,\n" +
            "            (select name from t_subject where code=t1.subjectid) as objSubjectName,\n" +
            "            (select name from t_crop where code=t1.breed) as objCropName,\n" +
            "            (select group_concat(imageURLContent)  from t_advinfoimg where advInfoId=t1.id ) as imgs, "+
            "(select showName from t_user where id=t1.userId) as objUserName,\n" +
            "(select puberty from t_puberty where id=t1.growthperiod) as objPubertyName,\n" +
            "t1.areacode as objAreaCode,\n" +
            "(select areaname from t_area where areacode= t1.areacode ) as objAreaName,\n" +
            "(select count(id) from t_advcollect where advId= t1.id ) as collectNum,\n" +
            "(select count(id) from t_advpraise where advInfoId= t1.id ) as praiseNum\n" +
            " from t_advisoryinfo t1\n" +
            "where t1.farmlandid=#{farmId} and t1.uploadTime between #{beginTime} and #{endTime} ")
    List<ObjectTlist> getObjByFarmIdToAdv(@Param("farmId")String farmId, @Param("beginTime")String beginTime, @Param("endTime")String endTime);


    /**
     * 获取灾情
     * */
    @Select("select t1.id as objId, t1.descript as objTitle, '3' as objType , date_format(t1.uploadTime ,'%Y-%m-%d %H:%i:%S') as objTime,t1.longitude as objLon ,t1.latitude as objLat,\n" +
            "t1.userId as objUserId,\n" +
            "             (select name from t_crop where code=t1.breed) as objCropName,\n" +
            "      (select group_concat(imageURLContent)  from t_disasterimg where disasterId=t1.id ) as imgs, "+
            "(select showName from t_user where id=t1.userId) as objUserName,\n" +
            "(select puberty from t_puberty where id=t1.growthperiod) as objPubertyName,\n" +
            "t1.areacode as objAreaCode,\n" +
            "(select areaname from t_area where areacode= t1.areacode ) as objAreaName,\n" +
            "(select name from t_disastertype where code=t1.disastertype) as objDisasterTypeName\n" +
            " from t_disaster t1\n" +
            "where t1.farm=#{farmId} and t1.uploadTime between #{beginTime} and #{endTime} \n" +
            "union  all\n" +
            "select t1.id as objId, t1.description as objTitle, '3' as objType , date_format(t1.recordtime ,'%Y-%m-%d %H:%i:%S')  as objTime,t1.longitude as objLon ,t1.latitude as objLat,\n" +
            "t1.userId as objUserId,\n" +
            "                         (select name from t_crop where code=t1.breed) as objCropName,\n" +
            "               replace(replace(replace(t1.img_url,'[\"',\"\"),'\"]',\"\"),'\"',\"\")  as imgs, " +
            "(select showName from t_user where id=t1.userId) as objUserName,\n" +
            "(select puberty from t_puberty where id=t1.growthperiod) as objPubertyName,\n" +
            "t1.areacode as objAreaCode,\n" +
            "(select areaname from t_area where areacode= t1.areacode ) as objAreaName,\n" +
            "(select name from t_disastertype where code=t1.disastertype) as objDisasterTypeName\n" +
            " from t_disaster_dip t1\n" +
            "where t1.farmlandid=#{farmId}  and t1.recordtime between #{beginTime} and #{endTime}")
    List<ObjectTlist> getObjByFarmIdTodes(@Param("farmId")String farmId, @Param("beginTime")String beginTime, @Param("endTime")String endTime);


    /**
     * 获取农事
     * */
    @Select("select id as objId, description as objTitle , date_format(recordtime ,'%Y-%m-%d %H:%i:%S') as objTime,longitude as objLon ,latitude as objLat,'4' as objType\n" +
            ",areacode as objAreaCode,\n" +
            "(select areaname from t_area where areacode= t1.areacode ) as objAreaName,\n" +
            " replace(replace(replace(img_url,'[\"',\"\"),'\"]',\"\"),'\"',\"\")  as imgs,userid as objUserId,\n" +
            "(select showName from t_user where id=t1.userid) as objUserName\n" +
            "from t_farming  t1\n" +
            " where farmlandid=#{farmId}  and recordtime between #{beginTime} and #{endTime}")
    List<ObjectTlist> getObjByFarmIdToFarming(@Param("farmId")String farmId, @Param("beginTime")String beginTime, @Param("endTime")String endTime);


    /**
     * 获取专家建议
     * */
    @Select("select tt1.* from (select id as objId,title as objTitle,info as objInfo, date_format(stime ,'%Y-%m-%d %H:%i:%S') as objTime,subjectid as objSubjectId,'5' as objType,\n" +
            "(select name from t_subject where code=t1.subjectid ) as objSubjectName,\n" +
            "(select puberty from t_puberty where id=t1.pubertyId) as objPubertyName,\n" +
            "(select name from t_crop where code=t1.crop) as objCropName\n" +
            " from t_suggest  t1   ) tt1\n" +
            " inner join t_suggest_farm t2\n" +
            "  on tt1.objId=t2.suggestId\n" +
            "  where t2.farm=#{farmId}  and tt1.objTime between #{beginTime} and #{endTime}")
    List<ObjectTlist> getObjByFarmIdToSugges(@Param("farmId")String farmId, @Param("beginTime")String beginTime, @Param("endTime")String endTime);

    /**
     * 获取农事活动
     * */
    @SelectProvider(type =TraceSourceMapperSQL.class,method = "getFarmingSQL")
    List<Map<String,Object>> getFarming(@Param("farmId")String farmId, @Param("beginTime")String beginTime, @Param("endTime")String endTime,@Param("type")Integer type);

    @Select("select t2.imageURLContent as img from  t_advisoryinfo t1 \n" +
            "inner join t_advinfoimg t2\n" +
            "on t1.id=t2.advInfoId\n" +
            " and t1.farmlandid=#{farmId} and t1.uploadTime between '${beginTime}' and '${endTime}'\n" +
            " union  all\n" +
            "select t2.imageURLContent as img from  t_agrinfo t1\n" +
            "inner join t_agrinfoimg t2\n" +
            "on t1.id=t2.agrInfoId\n" +
            "and t1.farm=#{farmId} and t1.createtime between '${beginTime}' and '${endTime}'\n" +
            "union  all\n" +
            "select replace(replace(replace(t1.img_url,'[\"',\"\"),'\"]',\"\"),'\"',\"\") as img  from t_farming t1 where t1.farmlandid =#{farmId} and t1.recordtime  between '${beginTime}' and '${endTime}'\n" +
            "union  all\n" +
            "select replace(replace(replace(t1.img_url,'[\"',\"\"),'\"]',\"\"),'\"',\"\") as img from t_disaster_dip t1 where t1.farmlandid =#{farmId}  and t1.createtime  between '${beginTime}' and '${endTime}'\n" +
            "union  all\n" +
            "select t2.imageURLContent as img from t_disaster t1\n" +
            "inner join t_disasterimg t2\n" +
            "on t1.id=t2.disasterId\n" +
            " and t1.farm =#{farmId}  and t1.createtime  between '${beginTime}' and '${endTime}'  limit 1\n" +
            "\n" +
            " ")
    String  getPubertyImg(@Param("farmId")String farmId, @Param("beginTime")String beginTime, @Param("endTime")String endTime);
    /**
     * 生育期
     * */
    @Select("select id as code,puberty as codeName from t_puberty t where t.crop = (select parentid from t_crop where code=(select crop from t_farmcrops where farmland=#{farmId})) order by t.indexid")
    List<Map<String,Object>> getPubertyByFarmId(@Param("farmId")String farmId);

    class  TraceSourceMapperSQL{
       public String  updateTraceSQL(Map<String,Object> map){
           TraceaBility traceaBility= (TraceaBility)map.get("traceaBility");
           StringBuilder stringBuilder= new StringBuilder();
           stringBuilder.append("update t_traceability set id="+traceaBility.getId()+"");
           if(traceaBility.getName()!=null){
               stringBuilder.append(" , name='"+traceaBility.getName()+"' ");
           }
           if(traceaBility.getStarttime()!=null){
               stringBuilder.append(" , starttime='"+traceaBility.getStarttime()+"' ");
           }
           if(traceaBility.getEndtime()!=null){
               stringBuilder.append(" , endtime='"+traceaBility.getEndtime()+"' ");
           }
           if(traceaBility.getState()!=null){
               stringBuilder.append(" , state='"+traceaBility.getState()+"' ");
           }
           if(traceaBility.getUrl()!=null){
               stringBuilder.append(" , url='"+traceaBility.getUrl()+"' ");
           }
           if(traceaBility.getWeburl()!=null){
               stringBuilder.append(" , weburl='"+traceaBility.getWeburl()+"' ");
           }
           stringBuilder.append(" where id="+traceaBility.getId()+"");

        return  stringBuilder.toString();
       }
        public String getFarmingSQL(Map<String,Object> map){
            String farmId=(String)map.get("farmId");
            String beginTime=(String)map.get("beginTime");
            String endTime=(String)map.get("endTime");
            Integer type=(Integer)map.get("type");
            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append("select date_format(t1.recordtime,'%Y-%m-%d %H:%i:%S') as `dateTime`,t2.name,replace(replace(replace(t1.img_url,'[\"',\"\"),'\"]',\"\"),'\"',\"\") as imgage,t3.showname,t1.description from t_farming  t1\n" +
                    "inner join t_farmingtype t2 " +
                    "on t1.`type`=t2.code  ");
            if(farmId!=null){
                stringBuilder.append( " and t1.farmlandid="+farmId+" " );
            }
            if(beginTime!=null&&endTime!=null){
                stringBuilder.append("  and  t1.recordtime between '"+beginTime+"' and  '"+endTime+"'  ");
            }
            if(type==1){
                //施肥
                stringBuilder.append("  and  t2.code=305 ");
            }else if(type==2){
                stringBuilder.append("  and  t2.code=309   and  t2.code=310   and  t2.code=311 ");
            }else if(type==3){
                stringBuilder.append("  and  t2.code not in (305,309,310,311) ");
            }

            stringBuilder.append(" inner join t_user t3 " +
                    "on t1.userid=t3.id  order by t1.recordtime desc ");
            return  stringBuilder.toString();
        }

        public String getTraceSourceListSQL(Map<String,Object> map){
            String farmId=(String)map.get("farmId");
            String areaCode=(String)map.get("areaCode");
            Integer state=(Integer)map.get("state");
            Integer userId=(Integer)map.get("userId");
            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append("select t1.id,t1.farm,t2.userId,t3.showName,t3.mobile,t3.HeadImage,t2.longitude,t2.latitude,t1.name,t1.starttime,t1.endtime,t1.state,t1.url,date_format(t1.createtime,'%Y-%m-%d %H:%i:%S') as createtime from t_traceability  t1\n" +
                    "                              inner join t_farmland t2\n" +
                    "                               on t1.farm=t2.id\n" +
                    "                               inner join t_user t3\n" +
                    "                               on t2.userId=t3.id where 1=1  ");
            if(farmId!=null){
                stringBuilder.append(" and  t1.farm="+farmId+" ");
            }
            if(userId!=null){
                stringBuilder.append(" and  t2.userId="+userId+" ");
            }
            if(state!=null){
                stringBuilder.append(" and t1.state="+state+" ");
            }
            if(areaCode!=null){
                stringBuilder.append(" and t2.code like concat("+areaCode+",'%') ");
            }
            return  stringBuilder.toString();
        }
    }
}
